package com.tmpt.service.utils;

import com.google.common.base.Strings;
import com.tmpt.utils.Config;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CommonDbDao {
	
	/**
	 * 功能：通过表名、字段信息拼接sql语句，执行插入操作
	 * @param table_name
	 * @param column_list
	 * @param conn
	 * @return 主键
	 * @throws SQLException
	 * @author wuxiaoyong
	 */
	public long addData(String table_name,List<Map<String,Object>> column_list,Connection conn) throws SQLException{
		//解析存储字段信息的List:column_list
		List column_name_list = new ArrayList();
		List column_value_list = new ArrayList();
		for(Map map : column_list){
			column_name_list.add(map.get(Column_Constant.COLUMN_NAME));
			column_value_list.add(map.get(Column_Constant.COLUMN_VALUE));
		}
		//拼接sql语句
		StringBuffer sb = new StringBuffer();
		sb.append("insert into ").append(table_name).append("(");
		for(Object c_name : column_name_list){
			sb.append(c_name).append(",");
		}
		sb.delete(sb.length()-1, sb.length());
		//
		sb.append(") values(");
		for(Object c_value : column_value_list){
			sb.append("?,");
		}
		sb.delete(sb.length()-1, sb.length());
		sb.append(")");
		//生成sql语句
		String sql = sb.toString();
		//.out.println(sql);
		//执行sql语句
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sql,pstmt.RETURN_GENERATED_KEYS);
			for(int i=0;i<column_value_list.size();i++){
				String columnDataType = (String)column_list.get(i).get(Column_Constant.DATA_TYPE);
				if(columnDataType != null && columnDataType.contains("int")){
					String strValue = (String)column_value_list.get(i);
					Integer value = Strings.isNullOrEmpty(strValue)?0:Integer.parseInt(strValue);
					pstmt.setInt(i + 1, value);
				}else if(columnDataType != null && columnDataType.contains("datetime")){
					pstmt.setTimestamp(i + 1, (Timestamp) column_value_list.get(i));
				}else if(columnDataType != null && columnDataType.contains("numeric")){
					String strValue = (String)column_value_list.get(i);
					BigDecimal value = Strings.isNullOrEmpty(strValue)?BigDecimal.ZERO:new BigDecimal(strValue);
					pstmt.setBigDecimal(i + 1, value);
				}else{
					pstmt.setObject(i + 1, column_value_list.get(i));
				}
			}
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			long id = rs.getInt(1);
			return id;
		}finally{
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
		}
	}

	/**
	 * 功能：通过表名、字段信息拼接sql语句，执行update操作
	 * @param table_name
	 * @param column_list
	 * @param conn
	 * @return 主键
	 * @throws SQLException
	 * @author wuxiaoyong
	 */
	public long updateData(String table_name, String id, List<Map<String,Object>> column_list,Connection conn) throws SQLException{
		//解析存储字段信息的List:column_list
		List column_name_list = new ArrayList();
		List column_value_list = new ArrayList();
		for(Map map : column_list){
			column_name_list.add(map.get(Column_Constant.COLUMN_NAME));
			column_value_list.add(map.get(Column_Constant.COLUMN_VALUE));
			////.out.println(map.get(Column_Constant.COLUMN_NAME)+"-----"+map.get(Column_Constant.COLUMN_VALUE));
		}
		//拼接sql语句
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(table_name).append(" set ");
		for(Object c_name : column_name_list){
			sb.append(c_name).append("=").append("?,");
		}
		sb.delete(sb.length()-1, sb.length());
		sb.append(" where id=?");
		//生成sql语句
		String sql = sb.toString();
		//.out.println(sql);
		//执行sql语句
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			StringBuilder sbColumns = new StringBuilder();
			//String sqlQueryTableName= "select column_name from information_schema.columns where table_name=?";
			String sqlQueryTableName = "";
			if(Config.db_dialect.equals("mysql")){
				sqlQueryTableName = "select column_name from information_schema.columns where table_name=?";
			}else if(Config.db_dialect.equals("postgresql")){
				sqlQueryTableName = "select column_name from information_schema.columns where table_name=?";
			}else if(Config.db_dialect.equals("mssqlserver")){
				sqlQueryTableName = "select name from syscolumns where id=object_id(?)";
			}
			pstmt = conn.prepareStatement(sqlQueryTableName);
			pstmt.setString(1,table_name);
			rs = pstmt.executeQuery();
			while(rs.next()){
				String cName = rs.getString(1);
				sbColumns.append(cName).append(",");
			}
			sbColumns.delete(sbColumns.length()-1, sbColumns.length());
			String columns = sbColumns.toString();
			
			//存在历史表
			/*if(hasTable(conn, table_name+"_history")){
				//insert into FinanceAccountSummaryT1_history select * from FinanceAccountSummaryT1 where id=1
				String sqlHistory = "insert into #table_name#_history(#columns#) select #columns# from #table_name# where id=#id#";
				sqlHistory = sqlHistory.replaceAll("#table_name#", table_name);
				sqlHistory = sqlHistory.replaceAll("#columns#", columns);
				sqlHistory = sqlHistory.replaceAll("#id#", id);
				//.out.println("--------sqlHistory:"+sqlHistory);
				pstmt = conn.prepareStatement(sqlHistory);
				pstmt.executeUpdate();
			}*/
			
			//update
			pstmt = conn.prepareStatement(sql,pstmt.RETURN_GENERATED_KEYS);
			for(int i=0;i<column_value_list.size();i++){
				String columnDataType = (String)column_list.get(i).get(Column_Constant.DATA_TYPE);
				if(columnDataType != null && columnDataType.contains("int")){
					String strValue = (String)column_value_list.get(i);
					Integer value = Strings.isNullOrEmpty(strValue)?0:Integer.parseInt(strValue);
					pstmt.setInt(i + 1, value);
				}else if(columnDataType != null && columnDataType.contains("datetime")){
					pstmt.setTimestamp(i + 1, (Timestamp) column_value_list.get(i));
				}else if(columnDataType != null && columnDataType.contains("numeric")){
					String strValue = (String)column_value_list.get(i);
					BigDecimal value = Strings.isNullOrEmpty(strValue)?BigDecimal.ZERO:new BigDecimal(strValue);
					pstmt.setBigDecimal(i + 1, value);
				}else{
					pstmt.setObject(i + 1, column_value_list.get(i));
				}
			}
			pstmt.setObject(column_value_list.size()+1, Integer.parseInt(id));
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			//long idReturn = rs.getInt(1);
			return Long.valueOf(id);
		}finally{
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
		}
	}
	
	/**
	 * 表是否存在
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException 
	 */
	public boolean hasTable(Connection conn, String tableName) throws SQLException{
		//查询历史表是否存在
		String url = "";
		if(Config.db_dialect.equals("mysql")){
			url = "select count(1) from information_schema.tables where table_name=? ";
		}else if(Config.db_dialect.equals("postgresql")){
			url = "select count(1) from pg_tables where tablename=? ";
		}else if(Config.db_dialect.equals("mssqlserver")){
			url = "select count(1) from sys.objects where name=? ";
		}
		//select count(1) from sys.objects where name = 'A2User'
		String sqlExistTableName= url;
		//.out.println(sqlExistTableName);
		PreparedStatement pstmt = conn.prepareStatement(sqlExistTableName);
		pstmt.setString(1, tableName);
		ResultSet rs = pstmt.executeQuery();
		int count = 0;
		if(rs.next()){
			count = rs.getInt(1);
		}
		if(count == 0){
			return false;
		}else{
			return true;
		}
	}
	
    //select count(1) from syscolumns where id=object_id('Test_44') and name='modifyMan1'
	public boolean hasColumn(Connection conn, String tableName, String columnName) throws SQLException{
		//查询历史表是否存在
		String url = "";
		if(Config.db_dialect.equals("mysql")){
			url = "select count(1) from information_schema.columns where table_name=?  and column_name=?";
		}else if(Config.db_dialect.equals("postgresql")){
			url = "select count(1) from information_schema.columns where table_name=? and column_name=? ";
		}else if(Config.db_dialect.equals("mssqlserver")){
			url = "select count(1) from syscolumns where id=object_id(?) and name=?";
		}
		String sqlExistTableColumn= url;
		//.out.println(sqlExistTableColumn);
		PreparedStatement pstmt = conn.prepareStatement(sqlExistTableColumn);
		pstmt.setString(1,tableName);
		pstmt.setString(2,columnName);
		ResultSet rs = pstmt.executeQuery();
		int count = 0;
		if(rs.next()){
			count = rs.getInt(1);
		}
		if(count == 0){
			return false;
		}else{
			return true;
		}
	}

	/**
	 * 多表情况下：
	 * 查询表字段属性
	 * @param conn
	 * @param tableNameList
	 * @return {"table_name_column_name":{"table_name":"","column_name":"","data_type":"int","length":"0"},...}
	 * @throws SQLException
	 */
	public Map<String,Map<String,String>> queryColumnsProperty(Connection conn, List<String> tableNameList) throws SQLException{
		Map<String, Map<String, String>> columnMap = new HashMap<>();

		for(String tableName:tableNameList) {
			String url = "";
			if (Config.db_dialect.equals("mysql")) {
				url = "select column_name as column_name ,data_type as data_type,CHARACTER_MAXIMUM_LENGTH as len from information_schema.columns where table_name=?";//  and column_name=?
			} else if (Config.db_dialect.equals("postgresql")) {
				url = "select * from information_schema.columns where table_name=?";// and column_name=?
			} else if (Config.db_dialect.equals("mssqlserver")) {
				url = "select * from syscolumns where id=object_id(?)";// and name=?
			}
			PreparedStatement pstmt = conn.prepareStatement(url);
			pstmt.setString(1, tableName);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String column_name = rs.getString("column_name");
				String data_type = rs.getString("data_type");
				String length = rs.getString("len");
				Map<String, String> cMap = new HashMap<>();
				cMap.put("table_name", tableName);
				cMap.put("column_name", column_name);
				cMap.put("data_type", data_type);
				cMap.put("length", length);
				columnMap.put(tableName + "_" + column_name, cMap);
			}
		}

		return columnMap;
	}
	
	/**
	 * 功能：通过传入sql和参数的方式做插入操作
	 * @param sql
	 * @param paraList
	 * @param conn
	 * @return 主键
	 * @throws SQLException
	 * @author wuxiaoyong
	 */
	public long addDataBySql(String sql, List paraList, Connection conn) throws SQLException{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sql,pstmt.RETURN_GENERATED_KEYS);

			int i=1;
			if(paraList!=null){
				for(Object o:paraList){
					pstmt.setObject(i++, o);
				}
			}
			
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			long id = rs.getInt(1);
			return id;
		}finally{
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
		}
	}
	
	/**
	 * 功能：更新表记录
	 * @param sql
	 * @param paraList
	 * @param conn
	 * @return 更新成功记录数
	 * @throws SQLException
	 * @author wuxiaoyong
	 */
	public int updateData(String sql, List paraList, Connection conn) throws SQLException{
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sql);

			int i=1;
			if(paraList!=null){
				for(Object o:paraList){
					pstmt.setObject(i++, o);
				}
			}
			
			int count = pstmt.executeUpdate();
			return count;
		}finally{
			if(pstmt!=null) pstmt.close();
		}
	}
	
	
	/**
	 * 功能：查询表，以List形式返回记录集
	 * @param sql
	 * @param paraList
	 * @param conn
	 * @return 记录集
	 * @throws SQLException
	 * @author wuxiaoyong
	 */
	public List<Map> queryList(String sql, List paraList, Connection conn) throws SQLException{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map> list = new ArrayList();
		try{
			pstmt = conn.prepareStatement(sql);
			int i=1;
			if(paraList!=null){
				for(Object o:paraList){
					pstmt.setObject(i++, o);
				}
			}

			rs = pstmt.executeQuery();
			while(rs.next()){
				HashMap map = new HashMap();
				ResultSetMetaData rsmd = rs.getMetaData();
				int count = rsmd.getColumnCount();
				for(int j=1; j<=count; j++){
					String columnName = rsmd.getColumnName(j);
					map.put(columnName, rs.getObject(columnName));
				}
				list.add(map);
			}
		}finally{
			if(rs!=null)rs.close();
			if(pstmt!=null)pstmt.close();
		}
		
		return list;
	}
	
	/**
	 * 功能：查询表中符合条件记录数
	 * @param sql
	 * @param paraList
	 * @param conn
	 * @return
	 * @throws SQLException
	 * @author wuxiaoyong
	 */
	public long queryCount(String sql, List paraList, Connection conn) throws SQLException{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		long count=0;
		try{
			pstmt = conn.prepareStatement(sql);
			int i=1;
			if(paraList!=null){
				for(Object o:paraList){
					pstmt.setObject(i++, o); 
				}
				 
			}
			
			rs = pstmt.executeQuery();
			rs.next();
			count = rs.getInt(1);
		}finally{
			if(rs!=null)rs.close();
			if(pstmt!=null)pstmt.close();
		}
		
		return count;
	}

	
	

}
